{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import jsonlines\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(109, 14)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.chdir(os.path.join(os.getcwd(), 'data'))\n",
    "\n",
    "df = pd.read_excel('markers.xlsx')\n",
    "df_board = pd.read_excel('board.xlsx')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 原始数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>short_name</th>\n",
       "      <th>desc</th>\n",
       "      <th>logo</th>\n",
       "      <th>images</th>\n",
       "      <th>phone</th>\n",
       "      <th>address</th>\n",
       "      <th>panorama</th>\n",
       "      <th>location</th>\n",
       "      <th>icon</th>\n",
       "      <th>position</th>\n",
       "      <th>scale</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>景点</td>\n",
       "      <td>孙中山铜像</td>\n",
       "      <td>铜像</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地址</td>\n",
       "      <td>2291779.0</td>\n",
       "      <td>23.634548,113.676900</td>\n",
       "      <td>tx@2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>5ce8fe1c29c7a8581bc1e98b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>景点</td>\n",
       "      <td>校训石</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.634037,113.677393</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>景点</td>\n",
       "      <td>南芳湖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.629113,113.679507</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>景点</td>\n",
       "      <td>西区公园</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.629958,113.677726</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>景点</td>\n",
       "      <td>大礼堂</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.631772,113.679662</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  type   name short_name  desc logo  images phone address   panorama  \\\n",
       "0   景点  孙中山铜像         铜像  地点描述  NaN     2.0   NaN      地址  2291779.0   \n",
       "1   景点    校训石        NaN  地点描述  NaN     1.0   NaN     NaN        NaN   \n",
       "2   景点    南芳湖        NaN  地点描述  NaN     2.0   NaN     NaN        NaN   \n",
       "3   景点   西区公园        NaN  地点描述  NaN     2.0   NaN     NaN        NaN   \n",
       "4   景点    大礼堂        NaN  地点描述  NaN     3.0   NaN     NaN        NaN   \n",
       "\n",
       "               location  icon  position  scale                       _id  \n",
       "0  23.634548,113.676900  tx@2       0.0   16.0  5ce8fe1c29c7a8581bc1e98b  \n",
       "1  23.634037,113.677393   NaN       NaN    NaN                       NaN  \n",
       "2  23.629113,113.679507   NaN       NaN    NaN                       NaN  \n",
       "3  23.629958,113.677726   NaN       NaN    NaN                       NaN  \n",
       "4  23.631772,113.679662   NaN       NaN    NaN                       NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>short_name</th>\n",
       "      <th>desc</th>\n",
       "      <th>logo</th>\n",
       "      <th>images</th>\n",
       "      <th>phone</th>\n",
       "      <th>address</th>\n",
       "      <th>panorama</th>\n",
       "      <th>location</th>\n",
       "      <th>icon</th>\n",
       "      <th>position</th>\n",
       "      <th>scale</th>\n",
       "      <th>_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>景点</td>\n",
       "      <td>孙中山铜像</td>\n",
       "      <td>铜像</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地址</td>\n",
       "      <td>2291779</td>\n",
       "      <td>23.634548,113.676900</td>\n",
       "      <td>tx@2</td>\n",
       "      <td>0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>5ce8fe1c29c7a8581bc1e98b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>景点</td>\n",
       "      <td>校训石</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>23.634037,113.677393</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>景点</td>\n",
       "      <td>南芳湖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>23.629113,113.679507</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>景点</td>\n",
       "      <td>西区公园</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>23.629958,113.677726</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>景点</td>\n",
       "      <td>大礼堂</td>\n",
       "      <td>NaN</td>\n",
       "      <td>地点描述</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>23.631772,113.679662</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  type   name short_name  desc logo  images phone address  panorama  \\\n",
       "0   景点  孙中山铜像         铜像  地点描述  NaN       2   NaN      地址   2291779   \n",
       "1   景点    校训石        NaN  地点描述  NaN       1   NaN     NaN         0   \n",
       "2   景点    南芳湖        NaN  地点描述  NaN       2   NaN     NaN         0   \n",
       "3   景点   西区公园        NaN  地点描述  NaN       2   NaN     NaN         0   \n",
       "4   景点    大礼堂        NaN  地点描述  NaN       3   NaN     NaN         0   \n",
       "\n",
       "               location  icon  position  scale                       _id  \n",
       "0  23.634548,113.676900  tx@2         0   16.0  5ce8fe1c29c7a8581bc1e98b  \n",
       "1  23.634037,113.677393   NaN         0    NaN                       NaN  \n",
       "2  23.629113,113.679507   NaN         0    NaN                       NaN  \n",
       "3  23.629958,113.677726   NaN         0    NaN                       NaN  \n",
       "4  23.631772,113.679662   NaN         0    NaN                       NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['images', 'panorama', 'position']] = df[['images', 'panorama', 'position']].fillna(0).astype('int')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 目标数据格式\n",
    "```\n",
    "{\n",
    "    type: '景点',\n",
    "    scale: 15.0,\n",
    "    icon: 'jt',\n",
    "    postion: 0,\n",
    "    data:   [\n",
    "        {\n",
    "            name: \"孙中山铜像\",\n",
    "            short_name: \"铜像\", \n",
    "            desc: \"中山铜像...\", \n",
    "            logo: \"tx\", \n",
    "            icon: \"tx@2\",\n",
    "            images: 3,\n",
    "            panorama: 0, \n",
    "            latitude: \"23.635875\",\n",
    "            longitude: \"113.678965\",\n",
    "            contact: { phone: \"020-123456\", address: \"出门左转\" }\n",
    "         },\n",
    "             ...\n",
    "         {...}\n",
    "      ]\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 填充NaN值\n",
    "df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>short_name</th>\n",
       "      <th>desc</th>\n",
       "      <th>logo</th>\n",
       "      <th>images</th>\n",
       "      <th>phone</th>\n",
       "      <th>address</th>\n",
       "      <th>panorama</th>\n",
       "      <th>icon</th>\n",
       "      <th>position</th>\n",
       "      <th>scale</th>\n",
       "      <th>_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>校区</td>\n",
       "      <td>中山大学南方学院</td>\n",
       "      <td>南苑</td>\n",
       "      <td>中山大学南方学院是2006年经教育部批准，由中山大学与广东珠江投资集团合作创办的独立学院，是...</td>\n",
       "      <td>nfsysu</td>\n",
       "      <td>2</td>\n",
       "      <td>020-61787201</td>\n",
       "      <td>广州市从化区温泉大道882号中山大学南方学院</td>\n",
       "      <td>2291778</td>\n",
       "      <td></td>\n",
       "      <td>11</td>\n",
       "      <td></td>\n",
       "      <td>Rok4jKCtqaeJQmkYMlvk1G30I46gM0TEpKX9c8DziJZzWewg</td>\n",
       "      <td>23.632674</td>\n",
       "      <td>113.679404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>探险</td>\n",
       "      <td>校长别墅</td>\n",
       "      <td></td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td>wkNEmpv7L4Th8yfd1Dl34gDzSLlnmLFGF16ACmQ7fIZAMzzV</td>\n",
       "      <td>23.635659</td>\n",
       "      <td>113.678386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>探险</td>\n",
       "      <td>后山</td>\n",
       "      <td></td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>climb</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.632258</td>\n",
       "      <td>113.673241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>探险</td>\n",
       "      <td>大礼堂之巅</td>\n",
       "      <td>天台风凉</td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.631629</td>\n",
       "      <td>113.679914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>探险</td>\n",
       "      <td>枫林晚</td>\n",
       "      <td></td>\n",
       "      <td>锦衣夜游好去处</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.636455</td>\n",
       "      <td>113.680172</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    type      name short_name  \\\n",
       "104   校区  中山大学南方学院         南苑   \n",
       "105   探险      校长别墅              \n",
       "106   探险        后山              \n",
       "107   探险     大礼堂之巅       天台风凉   \n",
       "108   探险       枫林晚              \n",
       "\n",
       "                                                  desc    logo  images  \\\n",
       "104  中山大学南方学院是2006年经教育部批准，由中山大学与广东珠江投资集团合作创办的独立学院，是...  nfsysu       2   \n",
       "105                                               地点描述               1   \n",
       "106                                               地点描述               1   \n",
       "107                                               地点描述               1   \n",
       "108                                            锦衣夜游好去处               1   \n",
       "\n",
       "            phone                 address  panorama   icon  position scale  \\\n",
       "104  020-61787201  广州市从化区温泉大道882号中山大学南方学院   2291778               11         \n",
       "105                                               0               10         \n",
       "106                                               0  climb         0         \n",
       "107                                               0                0         \n",
       "108                                               0                0         \n",
       "\n",
       "                                                  _id   latitude   longitude  \n",
       "104  Rok4jKCtqaeJQmkYMlvk1G30I46gM0TEpKX9c8DziJZzWewg  23.632674  113.679404  \n",
       "105  wkNEmpv7L4Th8yfd1Dl34gDzSLlnmLFGF16ACmQ7fIZAMzzV  23.635659  113.678386  \n",
       "106                                                    23.632258  113.673241  \n",
       "107                                                    23.631629  113.679914  \n",
       "108                                                    23.636455  113.680172  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# location\n",
    "df['latitude'] = df.apply(lambda x:x.location.split(',')[0], axis=1)\n",
    "df['longitude'] =df.apply(lambda x:x.location.split(',')[1], axis=1)\n",
    "df.drop('location', axis=1, inplace=True)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>short_name</th>\n",
       "      <th>desc</th>\n",
       "      <th>logo</th>\n",
       "      <th>images</th>\n",
       "      <th>panorama</th>\n",
       "      <th>icon</th>\n",
       "      <th>position</th>\n",
       "      <th>scale</th>\n",
       "      <th>_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>校区</td>\n",
       "      <td>中山大学南方学院</td>\n",
       "      <td>南苑</td>\n",
       "      <td>中山大学南方学院是2006年经教育部批准，由中山大学与广东珠江投资集团合作创办的独立学院，是...</td>\n",
       "      <td>nfsysu</td>\n",
       "      <td>2</td>\n",
       "      <td>2291778</td>\n",
       "      <td></td>\n",
       "      <td>11</td>\n",
       "      <td></td>\n",
       "      <td>Rok4jKCtqaeJQmkYMlvk1G30I46gM0TEpKX9c8DziJZzWewg</td>\n",
       "      <td>23.632674</td>\n",
       "      <td>113.679404</td>\n",
       "      <td>{'phone': '020-61787201', 'address': '广州市从化区温泉...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>探险</td>\n",
       "      <td>校长别墅</td>\n",
       "      <td></td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td>wkNEmpv7L4Th8yfd1Dl34gDzSLlnmLFGF16ACmQ7fIZAMzzV</td>\n",
       "      <td>23.635659</td>\n",
       "      <td>113.678386</td>\n",
       "      <td>{'phone': '', 'address': ''}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>探险</td>\n",
       "      <td>后山</td>\n",
       "      <td></td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>climb</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.632258</td>\n",
       "      <td>113.673241</td>\n",
       "      <td>{'phone': '', 'address': ''}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>探险</td>\n",
       "      <td>大礼堂之巅</td>\n",
       "      <td>天台风凉</td>\n",
       "      <td>地点描述</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.631629</td>\n",
       "      <td>113.679914</td>\n",
       "      <td>{'phone': '', 'address': ''}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>探险</td>\n",
       "      <td>枫林晚</td>\n",
       "      <td></td>\n",
       "      <td>锦衣夜游好去处</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>23.636455</td>\n",
       "      <td>113.680172</td>\n",
       "      <td>{'phone': '', 'address': ''}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    type      name short_name  \\\n",
       "104   校区  中山大学南方学院         南苑   \n",
       "105   探险      校长别墅              \n",
       "106   探险        后山              \n",
       "107   探险     大礼堂之巅       天台风凉   \n",
       "108   探险       枫林晚              \n",
       "\n",
       "                                                  desc    logo  images  \\\n",
       "104  中山大学南方学院是2006年经教育部批准，由中山大学与广东珠江投资集团合作创办的独立学院，是...  nfsysu       2   \n",
       "105                                               地点描述               1   \n",
       "106                                               地点描述               1   \n",
       "107                                               地点描述               1   \n",
       "108                                            锦衣夜游好去处               1   \n",
       "\n",
       "     panorama   icon  position scale  \\\n",
       "104   2291778               11         \n",
       "105         0               10         \n",
       "106         0  climb         0         \n",
       "107         0                0         \n",
       "108         0                0         \n",
       "\n",
       "                                                  _id   latitude   longitude  \\\n",
       "104  Rok4jKCtqaeJQmkYMlvk1G30I46gM0TEpKX9c8DziJZzWewg  23.632674  113.679404   \n",
       "105  wkNEmpv7L4Th8yfd1Dl34gDzSLlnmLFGF16ACmQ7fIZAMzzV  23.635659  113.678386   \n",
       "106                                                    23.632258  113.673241   \n",
       "107                                                    23.631629  113.679914   \n",
       "108                                                    23.636455  113.680172   \n",
       "\n",
       "                                               contact  \n",
       "104  {'phone': '020-61787201', 'address': '广州市从化区温泉...  \n",
       "105                       {'phone': '', 'address': ''}  \n",
       "106                       {'phone': '', 'address': ''}  \n",
       "107                       {'phone': '', 'address': ''}  \n",
       "108                       {'phone': '', 'address': ''}  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# contact\n",
    "df['contact'] = df.apply(lambda x:{\n",
    "    'phone': x.phone,\n",
    "    'address': x.address\n",
    "}, axis=1)\n",
    "df.drop(['phone', 'address'], axis=1, inplace=True)\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pinyin\n",
    "\n",
    "def get_pinyin_first_alpha(name):\n",
    "    '''\n",
    "        根据type中文名转为拼音缩写，用以匹配icon\n",
    "    '''\n",
    "    return \"\".join([i[0] for i in pinyin.get(name, \" \").split(\" \")])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [{'type': t, 'icon': get_pinyin_first_alpha(t), 'data': []} for t in set(df.type)] \n",
    "for index, row in df.iterrows():\n",
    "    for i in data:\n",
    "        if i['type'] == row.type:\n",
    "            i['data'].append(dict(row))\n",
    "            if row.scale != '':\n",
    "                i['scale'] = i['data'][0]['scale']\n",
    "            if row.position != '':\n",
    "                i['position'] = i['data'][0]['position']\n",
    "            if row._id != '':\n",
    "                i['_id'] = i['data'][0]['_id']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Board 处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 目标数据格式\n",
    "```\n",
    "{\n",
    "    'type': '信息类型',\n",
    "    'data': [\n",
    "        {\n",
    "            'time': 123456,   # timestamp\n",
    "            'content': '信息内容'\n",
    "        },\n",
    "        ...\n",
    "        {...}\n",
    "    ]\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>time</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>校园动态</td>\n",
       "      <td>123456</td>\n",
       "      <td>测试信息</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>校园动态</td>\n",
       "      <td>123456</td>\n",
       "      <td>测试信息2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>讲座论坛</td>\n",
       "      <td>123456</td>\n",
       "      <td>富文本信息</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   type    time content\n",
       "0  校园动态  123456    测试信息\n",
       "1  校园动态  123456   测试信息2\n",
       "2  讲座论坛  123456   富文本信息"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_board = df_board[df_board.status != 1]\n",
    "df_board.drop(columns='status', inplace=True)\n",
    "df_board.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'type': '讲座论坛',\n",
       "  'data': [{'type': '讲座论坛', 'time': 123456, 'content': '富文本信息'}]},\n",
       " {'type': '校园动态',\n",
       "  'data': [{'type': '校园动态', 'time': 123456, 'content': '测试信息'},\n",
       "   {'type': '校园动态', 'time': 123456, 'content': '测试信息2'}]}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_board = [{'type': t, 'data': []} for t in set(df_board.type)] \n",
    "for itme in data_board:\n",
    "    for index, row in df_board.iterrows():\n",
    "        if row.type == itme['type']:\n",
    "            itme['data'].append(dict(row))\n",
    "data_board"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出JSON\n",
    "需要注意以下几点：\n",
    "\n",
    "1. JSON 数据不是数组，而是类似 **[JSON Lines](http://jsonlines.org/)**，即各个记录对象之间使用 \\n 分隔，而非逗号；\n",
    "\n",
    "2. JSON 数据每个键值对的键名首尾不能是 .，例如 \".a\"、\"abc.\"，且不能包含多个连续的 .，例如 \"a..b\"；\n",
    "\n",
    "3. 键名不能重复，且不能有歧义，例如 {\"a\": 1, \"a\": 2} 或 {\"a\": {\"b\": 1}, \"a.b\": 2}；\n",
    "\n",
    "4. 时间格式须为 ISODate 格式，例如 \"date\": { \"$date\" : \"2018-08-31T17:30:00.882Z\" }；\n",
    "\n",
    "5. 当使用 Insert 冲突处理模式时，同一文件不能存在重复的 _id 字段，\u001c",
    "或与数据库已有记录相同的 _id 字段；\n",
    "\n",
    "6. CSV 格式的数据默认以第一行作为导入后的所有键名，余下的每一行则是与首行键名一一对应的键值记录。\n",
    "\n",
    "参考链接：\n",
    "* https://developers.weixin.qq.com/miniprogram/dev/wxcloud/guide/database/import.html?search-key=JSON%20lines  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "with jsonlines.open('data.json', mode='w') as writer:\n",
    "    for i in data:\n",
    "        writer.write(i)\n",
    "        \n",
    "with jsonlines.open('data_board.json', mode='w') as writer:\n",
    "    for i in data_board:\n",
    "        writer.write(i)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
